package com.template.core;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DbConnection {

	private Connection conn;

	public DbConnection(String driver, String url, String user, String pass) {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public DatabaseMetaData getDatabaseMetaData() {
		try {
			return conn.getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<TableBean> tables() {
		List<TableBean> tbs = new ArrayList<TableBean>();
		try {
			DatabaseMetaData dmd = getDatabaseMetaData();
			ResultSet rs = dmd.getTables(null, null, "",
					new String[] { "TABLE" });
			while (rs.next()) {
				TableBean tb = new TableBean();
				tb.setTableName(rs.getString("TABLE_NAME")); // 表明称
				primarys(tb); // 表主键
				exportedKeys(tb); // 导出主键
				importKeys(tb); // 导入主键
				columns(tb); // 表列
				tbs.add(tb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tbs;
	}

	public void columns(TableBean tb) {
		try {
			DatabaseMetaData dmd = getDatabaseMetaData();
			ResultSet rs = dmd.getColumns(null, null, tb.getTableName(), "");
			while (rs.next()) {
				ColumnBean cb = new ColumnBean();
				cb.setColumnName(rs.getString("COLUMN_NAME"));
				cb.setType(rs.getString("TYPE_NAME"));
				cb.setLength(rs.getInt("COLUMN_SIZE"));
				cb.setDigits(rs.getInt("DECIMAL_DIGITS"));
				cb.setDataType(rs.getInt("DATA_TYPE"));
				tb.addColumn(cb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void primarys(TableBean tb) {
		try {
			DatabaseMetaData dmd = getDatabaseMetaData();
			ResultSet rs = dmd.getPrimaryKeys(null, null, tb.getTableName());
			while (rs.next()) {
				PrimaryBean pb = new PrimaryBean();
				pb.setColumnName(rs.getString("COLUMN_NAME"));
				pb.setPrimaryName(rs.getString("PK_NAME")); // 主键名称
				tb.addPrimary(pb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void exportedKeys(TableBean tb) {
		try {
			DatabaseMetaData dmd = getDatabaseMetaData();
			ResultSet rs = dmd.getExportedKeys(null, null, tb.getTableName());
			while (rs.next()) {
				ExportedKeyBean ekb = new ExportedKeyBean();
				ekb.setPkTableName(rs.getString("PKTABLE_NAME"));
				ekb.setFkTableName(rs.getString("FKTABLE_NAME"));
				ekb.setFkColumnName(rs.getString("FKCOLUMN_NAME"));
				ekb.setPkColumnName(rs.getString("PKCOLUMN_NAME"));
				tb.addExportedKey(ekb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void importKeys(TableBean tb) {
		try {
			DatabaseMetaData dmd = getDatabaseMetaData();
			ResultSet rs = dmd.getImportedKeys(null, null, tb.getTableName());
			while (rs.next()) {
				ImportedKeyBean ikb = new ImportedKeyBean();
				ikb.setPkTableName(rs.getString("PKTABLE_NAME"));
				ikb.setFkTableName(rs.getString("FKTABLE_NAME"));
				ikb.setFkColumnName(rs.getString("FKCOLUMN_NAME"));
				ikb.setPkColumnName(rs.getString("PKCOLUMN_NAME"));
				tb.addImportedKey(ikb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static DbConnection getInstance() {
		DbConnection db = new DbConnection("com.mysql.jdbc.Driver",
				"jdbc:mysql://localhost:3306/naire", "root", "");
		return db;
	}

}
